end

as

第二种方法：

declare @table_name varchar(50)

declare @sql nvarchar(500)--此处要注意，声明的长度一定要够

delete from projectrangtree where type=4 and parentid not in(select id from projectrangtree where type=3)

select table_name from section_type

--删除section_settings的脏数据

declare del_cursor cursor scroll for

delete from projectrangtree where deleteversion>0

close del_cursor

while (@@fetch_status=0)

--print @table_name

delete from section_settings where parent_prj_tree_id not in(select id from projectrangtree)

复制代码 代码如下:

--begin

--declare @id int

set @Sql = N'delete from '+@tableName

--delete from (select talbe_name from section_type) where measuring_point_id not in (select id from measuring_point_setting)

--delete from @table_name where measuring_point_id not in (select id from measuring_point_setting)

--print @table_name

WHILE(@@FETCH_STATUS = 0)

--deallocate test_cursor

Fetch Next From curTable Into @tableName

--删除projectrangtree的脏数据

--print @id

Fetch Next From curTable Into @tableName

复制代码 代码如下:

--while @@fetch_status=0

for select Table_Name from information_schema.tables where TABLE_TYPE='BASE TABLE'
BEGIN

--declare @table_name nvarchar(50)

--close test_cursor

begin

--print quotename(@table_name)

--end

CREATE proc [dbo].[delAllRecord]

--删除各个表里的测点

Open curTable

--open test_cursor

--select id,table_name from dbo.section_type

declare @tableName nvarchar(255)

--fetch next from test_cursor into @id,@table_name

--exec sp_executesql @measuring_point_id

CLOSE curTable
DEALLOCATE curTable

exec sp_executesql @sql

open del_cursor

end

delete from projectrangtree where type=5 and parentid not in(select id from projectrangtree where type=4)

fetch next from del_cursor into @table_name

set @sql = 'delete from '+ quotename(@table_name) +' where measuring_point_id not in(select id from measuring_point_setting)'

delete from projectrangtree where type=3 and parentid not in(select id from projectrangtree where type=2)

--set @measuring_point_id='select measuring_point_id from '+quotename(@table_name)

deallocate del_cursor

exec sp_executesql @sql

--declare @measuring_point_id nvarchar(500)

declare @Sql nvarchar(255)

--declare test_cursor cursor scroll for

Declare curTable Cursor

fetch next from del_cursor into @table_name